SET NOCOUNT ON;
go
/*********************************************************************************************************
*Created/Source by	:sfibich
*ScriptName			:performance.table.Ring_Buffer_Scheduler_Montior.sql													
*Version			:1.0.120813
*Purpose			:generates a table to hold data from the sys.dm_os_ring_buffer DMV.  In particular
*					 it holds data XML data from the record column where the 
*					 ring_buffer_type='RING_BUFFER_SCHEDULER_MONITOR'
*					 
*********************************************************************************************************/
go

BEGIN TRAN
BEGIN TRY

if exists (select 1 from sys.tables where name='Ring_Buffer_Scheduler_Monitor' 
	and schema_name(schema_id)='performance')
BEGIN
	if not exists (select * from metadata.utilityInfo where 
				info_desc=('performance.Ring_Buffer_Scheduler_Monitor') 
				and value='1.0.120813')
	BEGIN
		drop table performance.Ring_Buffer_Scheduler_Monitor;
	END
END

if not exists (select * from sys.tables where name='Ring_Buffer_Scheduler_Monitor' 
	and schema_id=schema_id('performance'))
BEGIN
	create table performance.Ring_Buffer_Scheduler_Monitor (
	Ring_Buffer_Scheduler_Montiory_Id int identity(1,1)
	,Record_Date_Time datetime not null
	,Process_Utilization_Percent tinyInt not null
	,System_Idle_Percent tinyInt not null
	,Memory_Utilization_Percent tinyint not null
	,Page_Faults int not null
	,Working_Set_Delta int not null
	,constraint pk_Ring_Buffer_Scheduler_Monitor primary key (Ring_Buffer_Scheduler_Montiory_Id)
	);
	
	merge metadata.utilityInfo as t
	using (
	select 
	'performance.Ring_Buffer_Scheduler_Monitor' as info_desc
	,'1.0.120813' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
END

COMMIT TRAN
END TRY
BEGIN CATCH
	PRINT 'ERROR_NUMBER:'+cast(ERROR_NUMBER() as varchar(10))
    PRINT 'ERROR_MESSAGE:'+ERROR_MESSAGE() 

	if @@TRANCOUNT >0 
		ROLLBACK TRAN
END CATCH	

